import { mysqlTable, varchar, timestamp, int, decimal, bigint } from 'drizzle-orm/mysql-core'
import { createInsertSchema } from 'drizzle-zod'
import * as z from 'zod'
import {
  defaultPageZodConfig,
  enumValidation,
  idCardValidation,
  moneyValidation,
  phoneValidation,
  stringValidation
} from '../config/zod'
import { relations } from 'drizzle-orm'
import { caseRecordTable } from './caseRecord'

// 案件表定义
export const casesTable = mysqlTable('case', {
  id: bigint('id', { mode: 'bigint' }).autoincrement().primaryKey(),
  orderId: varchar({ length: 100 }).notNull(), // 订单编号
  batch: varchar({ length: 100 }).notNull(), // 批次
  clientName: varchar({ length: 100 }).notNull(), // 原告
  plaintiff: varchar({ length: 100 }).notNull(), // 诉讼主体
  name: varchar({ length: 100 }).notNull(), // 被告姓名
  idCard: varchar({ length: 20 }).notNull(), // 被告身份证
  phone: varchar({ length: 100 }).notNull(), // 被告手机号
  address: varchar({ length: 100 }).notNull(), // 户籍地址
  lender: varchar({ length: 100 }).notNull(), // 放款方
  guarantor: varchar({ length: 100 }).notNull(), // 担保方
  overduePrincipal: decimal({ precision: 15, scale: 2 }).notNull(), // 逾期本金
  litigationTeam: varchar({ length: 100 }).notNull(), // 诉讼组
  legalAssistant: varchar({ length: 100 }).notNull(), // 律师助理
  mediator: varchar({ length: 100 }).notNull(), // 调解员
  court: varchar({ length: 100 }).notNull(), // 法院
  caseStatus: int().default(CaseStatusEnum.待分配).notNull(), // 案件状态
  orderType: int().default(OrderTypeEnum.主订单).notNull(), // 订单类型
  createdAt: timestamp('created_at', { mode: 'date' }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { mode: 'date' }).defaultNow().onUpdateNow().notNull()
})

// 案件 Schema
const baseCaseSchema = createInsertSchema(casesTable, {
  idCard: idCardValidation,
  phone: phoneValidation,
  overduePrincipal: moneyValidation,
  orderId: stringValidation('订单编号'),
  batch: stringValidation('批次'),
  clientName: stringValidation('委托方'),
  plaintiff: stringValidation('诉讼主体'),
  name: stringValidation('姓名'),
  address: stringValidation('户籍地址'),
  lender: stringValidation('放款方'),
  guarantor: stringValidation('担保方'),
  litigationTeam: stringValidation('诉讼组'),
  legalAssistant: stringValidation('律师助理'),
  mediator: stringValidation('调解员'),
  court: stringValidation('法院'),
  caseStatus: enumValidation(CaseStatusEnum, '案件状态'),
  orderType: enumValidation(OrderTypeEnum, '订单类型')
})

// 创建案件 Schema (新建)
export const createCaseSchema = baseCaseSchema.omit({
  id: true,
  createdAt: true,
  updatedAt: true
})

// 更新案件 Schema
export const updateCaseSchema = baseCaseSchema
  .omit({
    id: true,
    createdAt: true,
    updatedAt: true
  })
  .partial()

// 批量导入请求 Schema
export const batchImportSchema = z.object({
  file: z.instanceof(File).refine((file) => {
    const allowedTypes = [
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'application/vnd.ms-excel'
    ]
    const isValidType = allowedTypes.includes(file.type)
    return isValidType
  }, '请上传 Excel 文件 (.xlsx, .xls)'),
  fileName: z.string().min(1, '文件名称不能为空').max(255, '文件名称不能超过255个字符')
})

// 案子列表查询 Schema
export const caseListQuerySchema = baseCaseSchema
  .pick({
    clientName: true,
    batch: true,
    orderType: true
  })
  .extend(defaultPageZodConfig)

// 案件表的关联关系
export const casesRelations = relations(casesTable, ({ many }) => ({
  record: many(caseRecordTable)
}))
